﻿'*******************************************************************
' Program: Data Center Visio Rack Tool
' Author: Albert E Edlund
' Date:
'
' Purpose:
' http://www.opengeospatial.org/standards
'
' INSERT into tblattrGeoData (geoSpatial) 
' VALUES (geography::STGeomFromText('POINT(-87.62778997421264 41.882023079055145)', 4326))
'
' INSERT into tblattrGeoData (geoSpatial) 
' VALUES (geography::STGeomFromText('POINT(-88.14794540405273 41.77140769972487)', 4326))
'
' INSERT into tblNetworkLink (linkSpatial) 
' VALUES (geography::STGeomFromText('LINESTRING(-88.14828872680664 41.77079957087898, -87.62774705886841 41.88167161294157)', 4326))
'
'
'*******************************************************************

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling

Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.ObjectModel


Imports System.Data
Imports System.IO
Imports System.Xml

Imports System.Runtime.InteropServices


Imports System.Drawing.Image
Imports Microsoft.SqlServer.Types
Imports DC_Rack.Objects.clsDC_RackData
Imports System.Data.SqlTypes


Public Class clsAttrGeoData

#Region " Properties and declarations "

    Public m_db As Database
    Public m_ds As DataSet
    Public m_dt As DataTable
    Private strUserId As String

    Public m_dc As dc_GeoData
    Public m_oc As Collection(Of dc_GeoData)


    Public m_deleteCommand As DbCommand
    Public m_insertCommand As DbCommand
    Public m_updateCommand As DbCommand



    Public Const readAll As String = "selectTblAttrGeoData"

    ' who am I
    Private myEnum As DC_RackTables = DC_RackTables.tblAttrGeoData

#End Region

#Region " Move/Transform Data "

    '
    ' create columns
    '
    Public Sub CreateTableColumns(ByRef tempTable As DataTable)

        Try

            Dim colgeoKey As New System.Data.DataColumn
            colgeoKey.ColumnName = "geoKey"
            colgeoKey.DataType = GetType(Integer)
            colgeoKey.DefaultValue = m_dc.geoKey
            tempTable.Columns.Add(colgeoKey)

            Dim colgeoSiteKey As New System.Data.DataColumn
            colgeoSiteKey.ColumnName = "geoSiteKey"
            colgeoSiteKey.DataType = GetType(Integer)
            colgeoSiteKey.DefaultValue = m_dc.geoSiteKey
            tempTable.Columns.Add(colgeoSiteKey)

            Dim colgeoLocKey As New System.Data.DataColumn
            colgeoLocKey.ColumnName = "geoLocKey"
            colgeoLocKey.DataType = GetType(Integer)
            colgeoLocKey.DefaultValue = m_dc.geoLocKey
            tempTable.Columns.Add(colgeoLocKey)

            Dim colgeoSpatial As New System.Data.DataColumn
            colgeoSpatial.ColumnName = "geoSpatial"
            colgeoSpatial.DataType = GetType(Microsoft.SqlServer.Types.SqlGeography)
            colgeoSpatial.DefaultValue = Nothing
            tempTable.Columns.Add(colgeoSpatial)

            Dim colgeotxtSpatial As New System.Data.DataColumn
            colgeotxtSpatial.ColumnName = "geotxtSpatial"
            colgeotxtSpatial.DataType = GetType(String)
            colgeotxtSpatial.DefaultValue = m_dc.geotxtSpatial
            tempTable.Columns.Add(colgeotxtSpatial)

            Dim colgeoLongitude As New System.Data.DataColumn
            colgeoLongitude.ColumnName = "geoLongitude"
            colgeoLongitude.DataType = GetType(Double)
            colgeoLongitude.DefaultValue = m_dc.geoLongitude
            tempTable.Columns.Add(colgeoLongitude)

            Dim colgeoLatitude As New System.Data.DataColumn
            colgeoLatitude.ColumnName = "geoLatitude"
            colgeoLatitude.DataType = GetType(Double)
            colgeoLatitude.DefaultValue = m_dc.geoLatitude
            tempTable.Columns.Add(colgeoLatitude)

            Dim colgeoComments As New System.Data.DataColumn
            colgeoComments.ColumnName = "geoComments"
            colgeoComments.DataType = GetType(String)
            colgeoComments.DefaultValue = m_dc.geoComments
            tempTable.Columns.Add(colgeoComments)

            Dim colgeoLastUpdate As New System.Data.DataColumn
            colgeoLastUpdate.ColumnName = "geoLastUpdate"
            colgeoLastUpdate.DataType = GetType(Date)
            colgeoLastUpdate.DefaultValue = m_dc.geoLastUpdate
            tempTable.Columns.Add(colgeoLastUpdate)

            Dim colgeoLastEdit As New System.Data.DataColumn
            colgeoLastEdit.ColumnName = "geoLastEdit"
            colgeoLastEdit.DataType = GetType(String)
            colgeoLastEdit.DefaultValue = m_dc.geoLastEdit
            tempTable.Columns.Add(colgeoLastEdit)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' use for writing records
    '
    Public Sub movePropertiesToDataRow _
                   (ByRef tempRow As DataRow, _
                    ByVal tempDC As dc_GeoData)
        Try

            With tempDC
                tempRow.SetField("geoKey", .geoKey)
                tempRow.SetField("geoSiteKey", .geoSiteKey)
                tempRow.SetField("geoLocKey", .geoLocKey)
                tempRow.SetField("geotxtSpatial", .geotxtSpatial)
                tempRow.SetField("geoSpatial", .geoSpatial)
                tempRow.SetField("geoLongitude", .geoLongitude)
                tempRow.SetField("geoLatitude", .geoLatitude)
                tempRow.SetField("geoComments", .geoComments)
                tempRow.SetField("geoLastUpdate", .geoLastUpdate)
                tempRow.SetField("geoLastEdit", .geoLastEdit)
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties(ByRef tempRow As DataRow)

        Try



            ' if the geoSpatial comes in as a string, read the txt version and 
            ' convert it (default geottxtSpatial should be "POINT EMPTY"
            Dim geoData As SqlGeography = Nothing
            Dim typeSpatial As System.Type = tempRow.Item("geoSpatial").GetType
            If LCase(typeSpatial.Name) = "string" Then
                Dim strSql As SqlString = New SqlString(tempRow.Item("geotxtSpatial"))
                Dim arrSqlChar As SqlChars = New SqlChars(strSql)
                ' use the STPointFromText as a format check of the geography data (this table should be point information)
                geoData = geoData.STPointFromText(arrSqlChar, 4326)
            Else
                geoData = tempRow.Item("geoSpatial")
            End If


            With m_dc
                .geoKey = tempRow.Item("geoKey")
                .geoSiteKey = tempRow.Item("geoSiteKey")
                .geoLocKey = tempRow.Item("geoLocKey")
                .geotxtSpatial = tempRow.Item("geotxtSpatial")
                .geoSpatial = geoData
                .geoLongitude = tempRow.Item("geoLongitude")
                .geoLatitude = tempRow.Item("geoLatitude")
                .geoComments = tempRow.Item("geoComments")
                .geoLastUpdate = tempRow.Item("geoLastUpdate")
                .geoLastEdit = tempRow.Item("geoLastEdit")
            End With

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
        (ByRef tempRow As DataRow, _
         ByRef tempDC As dc_GeoData)

        Try


            ' if the geoSpatial comes in as a string, read the txt version and 
            ' convert it 
            Dim geoData As SqlGeography = Nothing
            Dim typeSpatial As System.Type = tempRow.Item("geoSpatial").GetType
            If LCase(typeSpatial.Name) = "string" Then
                Dim strSql As SqlString = New SqlString(tempRow.Item("geotxtSpatial"))
                Dim arrSqlChar As SqlChars = New SqlChars(strSql)
                geoData = geoData.STPointFromText(arrSqlChar, 4326)
            Else
                geoData = tempRow.Item("geoSpatial")
            End If


            With tempDC
                .geoKey = tempRow.Item("geoKey")
                .geoSiteKey = tempRow.Item("geoSiteKey")
                .geoLocKey = tempRow.Item("geoLocKey")
                .geotxtSpatial = tempRow.Item("geotxtSpatial")
                .geoSpatial = tempRow.Item("geoSpatial")
                .geoLongitude = tempRow.Item("geoLongitude")
                .geoLatitude = tempRow.Item("geoLatitude")
                .geoComments = tempRow.Item("geoComments")
                .geoLastUpdate = tempRow.Item("geoLastUpdate")
                .geoLastEdit = tempRow.Item("geoLastEdit")
            End With

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub moveDataTableToObservableCollection _
            (ByVal dtTemp As DataTable)

        Try

            If 0 < dtTemp.Rows.Count Then

                If m_oc IsNot Nothing Then
                    m_oc = Nothing
                    m_oc = New Collection(Of dc_GeoData)
                Else
                    m_oc = New Collection(Of dc_GeoData)
                End If

                Dim drTemp As DataRow
                Dim dcTemp As dc_GeoData
                For Each drTemp In dtTemp.Rows
                    dcTemp = New dc_GeoData
                    moveDataRowToProperties(drTemp, dcTemp)
                    m_oc.Add(dcTemp)
                Next

            End If

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Public Function moveDataTableToXML() As XmlDocument

        Dim writer = New System.IO.StringWriter
        Dim m_dt_xml As Xml.XmlDocument = Nothing

        Try


            m_dt.WriteXml(writer, XmlWriteMode.WriteSchema, True)
            m_dt_xml = Nothing
            m_dt_xml = New Xml.XmlDocument
            m_dt_xml.LoadXml(writer.ToString)

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            writer = Nothing

        End Try

        Return m_dt_xml


    End Function

#End Region

#Region " standard calls "


    '
    '
    '
    Public Sub initDataSetAndTable()

        Try

            ' make sure the root stuff is there
            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema

            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            If m_oc Is Nothing Then
                m_oc = New Collection(Of dc_GeoData)
            Else
                m_oc = Nothing
                m_oc = New Collection(Of dc_GeoData)
            End If

            If m_dc Is Nothing Then
                m_dc = New dc_GeoData(strUserId)
            Else
                m_dc = New dc_GeoData(strUserId)
            End If

            CreateTableColumns(m_dt)
            m_dt.TableName = DC_RackTables.tblAttrGeoData.ToString
            m_ds.Tables.Add(m_dt)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Private Sub buildUpdateAttrGeoDataCommand()

        Try
            m_updateCommand = m_db.GetStoredProcCommand("updateAttrGeoData")

            Dim cmdActive As DbCommand = m_updateCommand
            m_db.AddInParameter(cmdActive, "geoKey", DbType.Int32, "geoKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoSiteKey", DbType.Int32, "geoSiteKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoLocKey", DbType.Int32, "geoLocKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoComments", DbType.String, "geoComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geotxtSpatial", DbType.String, "geotxtSpatial", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoLastUpdate", DbType.Date, "geoLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoLastEdit", DbType.String, "geoLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildInsertAttrGeoDataCommand()

        Try
            m_insertCommand = m_db.GetStoredProcCommand("insertAttrGeoData")

            Dim cmdActive As DbCommand = m_insertCommand

            m_db.AddInParameter(cmdActive, "geoSiteKey", DbType.Int32, "geoSiteKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoLocKey", DbType.Int32, "geoLocKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoComments", DbType.String, "geoComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geotxtSpatial", DbType.String, "geotxtSpatial", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoLastUpdate", DbType.Date, "geoLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "geoLastEdit", DbType.String, "geoLastEdit", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildDeleteAttrGeoDataCommand()

        Try
            m_deleteCommand = m_db.GetStoredProcCommand("deleteAttrGeoData")

            Dim cmdActive As DbCommand = m_deleteCommand
            m_db.AddInParameter(cmdActive, "geoKey", DbType.Int32, "geoKey", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' create a record based on property values move it to the generic datatable
    ' then copy the table to the database
    '
    Public Function addGeoDataDataSourceRecord _
            (ByVal tempDC As dc_GeoData, _
             Optional ByVal blnSilent As Boolean = False) _
             As Integer

        Dim intRowsAffected As Integer = 0

        Try

            initDataSetAndTable()

            Dim m_dr As DataRow = m_ds.Tables(DC_RackTables.tblAttrGeoData.ToString).NewRow
            movePropertiesToDataRow(m_dr, tempDC)
            m_ds.Tables(DC_RackTables.tblAttrGeoData.ToString).Rows.Add(m_dr)
            intRowsAffected = dbUpdateDataSet()

            If blnSilent = False Then
                MsgBox("DataRowsUpated = " & intRowsAffected)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return intRowsAffected

    End Function

    '
    '
    '
    Public Function dbUpdateDataSet() As Int16

        Dim rowsAffected As Int16 = 0

        Try

            rowsAffected = m_db.UpdateDataSet(m_ds, DC_RackTables.tblAttrGeoData.ToString, _
                                        m_insertCommand, _
                                        m_updateCommand, _
                                        m_deleteCommand, _
                                        UpdateBehavior.Standard)
            'msgbox rowsAffected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return rowsAffected

    End Function

#End Region

#Region " database calls "

    '
    ' all the geo data
    '
    Public Sub dbSelectAllGeoData()


        Try

            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand(readAll)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrGeoData.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrGeoData.ToString)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub


    '
    ' geo data based on loc (probably a campus view)
    '
    Public Sub dbSelectAttrGeoDataByLocKey(ByVal intKey As Int32)


        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAttrGeoDataByLocKey")
            m_db.AddInParameter(cmd, "@geolocKey", DbType.Int32, intKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrGeoData.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrGeoData.ToString)

            If m_dt.Rows.Count = 1 Then
                Dim m_dr As System.Data.DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub

    '
    ' geo data base on site
    '
    Public Sub dbSelectAttrGeoDataBySiteKey(ByVal intKey As Int32)


        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAttrGeoDataBySiteKey")
            m_db.AddInParameter(cmd, "@geositeKey", DbType.Int32, intKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrGeoData.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrGeoData.ToString)

            If m_dt.Rows.Count = 1 Then
                Dim m_dr As System.Data.DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub

    '
    ' geo data base on site
    '
    Public Sub dbSelectAttrGeoDataByGeoKey(ByVal intKey As Int32)


        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAttrGeoDataByGeoKey")
            m_db.AddInParameter(cmd, "@geoKey", DbType.Int32, intKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrGeoData.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrGeoData.ToString)

            If m_dt.Rows.Count = 1 Then
                Dim m_dr As System.Data.DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub



    '
    ' uses the stGeomFromText, stPointFromText
    ' longitude/latitude
    '
    Public Sub dbUpdateAttrGeoDataGeographyPoint _
            (ByVal intGeoKey As Int32, _
            ByVal strWKT As String, _
            ByVal intSRID As Int32)


        Try


            Dim cmd As DbCommand = m_db.GetStoredProcCommand("updateAttrGeoDataGeography")
            m_db.AddInParameter(cmd, "@geoKey", DbType.Int32, intGeoKey)
            m_db.AddInParameter(cmd, "@wkt", DbType.String, strWKT)
            m_db.AddInParameter(cmd, "@srid", DbType.Int32, intSRID)

            Dim affectedRows As Int16 = m_db.ExecuteNonQuery(cmd)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub





#End Region

#Region " Constructor "



    Public Sub New()
        Try


            m_db = DatabaseFactory.CreateDatabase()



            '
            ' init an object class so that the default constants are available
            ' to the table definitions
            '

            If m_dc IsNot Nothing Then
                m_dc = Nothing
                m_dc = New dc_GeoData(strUserId)
            Else
                m_dc = New dc_GeoData(strUserId)
            End If

            initDataSetAndTable()

            m_updateCommand = Nothing
            m_insertCommand = Nothing
            m_deleteCommand = Nothing

            buildInsertAttrGeoDataCommand()
            buildDeleteAttrGeoDataCommand()
            buildUpdateAttrGeoDataCommand()

            strUserId = Environment.UserDomainName & "/" & Environment.UserName

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


#End Region



End Class
